********************************************************************************
*Project:  	Swiss Trade During the Covid-19 Pandemic: An Early Appraisal	
*Authors:	KB, SL, VP, PW											
*This File: Table 2/3
*Version: 	vFinal						
*Date: 		2020-11-01			
********************************************************************************



********************************************************************************
********************************************************************************
*Prepare Data Trade
********************************************************************************
********************************************************************************

use "${root}\workfiles\trade_2002_2020_M_analysis_wide.dta", replace

*!!!SELECT TIME PERIOD (MONTHS)
keep if SAMPLE==21
keep if monat<=6
keep if jahr>=2019

*Trade Variable
gen trade_chf_mia=export_chf_mia+import_chf_mia

keep jahr monat datum land_id land_str land_iso2								///
export_chf_mia import_chf_mia trade_chf_mia

gen time_monat=monat
replace time_monat=monat+12 if jahr==2020

tsset land_id time_monat

foreach var of varlist export_chf_mia import_chf_mia trade_chf_mia {
	gen `var'_dpct=(`var'-l12.`var')/(l12.`var')*100
}

keep if jahr==2020

keep jahr monat time_monat land_id land_str land_iso2 export_chf_mia_dpct import_chf_mia_dpct trade_chf_mia_dpct
order jahr monat time_monat land_id land_str land_iso2 export_chf_mia_dpct import_chf_mia_dpct trade_chf_mia_dpct
sort land_str

save "${root}\temp\reg_panel_MONTHS.dta", replace

*ADD 2019 TOTAL TRADE FOR SAMPLE RESTRICTIONS
use "${root}\workfiles\trade_2002_2020_M_analysis_wide.dta", replace

keep if SAMPLE==21
gen trade_chf_mia=export_chf_mia+import_chf_mia

foreach x in 10 25 40 50 {
    local y=`x'/1000
	gen trade_Q1_Q2_`x'mio=.
	replace trade_Q1_Q2_`x'mio=1 if trade_chf_mia>=`y' & monat>=1 & monat<=6
}

collapse (sum) export_chf_mia import_chf_mia trade_chf_mia trade_Q1_Q2*, by(jahr land_id land_str land_iso2)

keep if jahr==2019
keep  land_iso2 export_chf_mia import_chf_mia trade_chf_mia trade_Q1_Q2*

merge 1:m land_iso2 using "${root}\temp\reg_panel_MONTHS.dta"
keep if _merge==3
drop _merge

keep jahr monat time_monat 														///
land_str land_iso2 export_chf_mia import_chf_mia trade_chf_mia 	trade_Q1_Q2*	///
export_chf_mia_dpct import_chf_mia_dpct trade_chf_mia_dpct

order jahr monat time_monat land_str land_iso2 export_chf_mia import_chf_mia trade_chf_mia 			///
export_chf_mia_dpct import_chf_mia_dpct trade_chf_mia_dpct trade_Q1_Q2*

rename *_chf_mia *_chf_mia_2019

sort land_str time_monat

save "${root}\temp\reg_panel_trade_MONTHS.dta", replace


********************************************************************************
********************************************************************************
*Prepare PMI 
********************************************************************************
********************************************************************************

import delimited "${root}\data\PMI_KS_Data\pmi_dta_raw.csv", clear 

rename *manpm country_*
rename usmanmepm country_us

*Date variables
split time, parse("-")
destring time1, gen(year)
destring time2, gen(month)
destring time3, gen(day)

drop time*

gen date=mdy(month, day, year)
format date %tddd.nn.YY
sort date

*Main PMI variable
foreach var of varlist country_at-country_vn {
	sum `var'
	gen `var'_std=(`var'-r(mean))/r(sd)
}

keep if year>2018

rename country_*_std country_std_*
reshape long country_ country_std_, i(date) j(country) string

rename country_ pmi
rename country_std_ pmi_std
replace country=strupper(country)
egen land_id=group(country)
rename country land_iso2

order land_iso2 date year month day pmi pmi_std
sort land_iso2 date year month day pmi pmi_std

*MAIN TIME ID
gen time_monat=month
replace time_monat=month+12 if year==2020

tsset land_id time_monat

foreach var of varlist pmi {
	gen `var'_dpct=(`var'-l12.`var')/(l12.`var')*100
}

foreach var of varlist pmi_std {
	gen `var'_delta=(`var'-l12.`var')
}

keep if year==2020
keep if month<=6
drop land_id
order land_iso2 year pmi pmi_std pmi_dpct pmi_std_delta

merge 1:1 land_iso2 time_monat using "${root}\temp\reg_panel_trade_MONTHS.dta"
drop if _merge==1
drop _merge

drop year month day date

order jahr monat time_monat land_str land_iso2 									///
export_chf_mia_dpct import_chf_mia_dpct trade_chf_mia_dpct 						///
pmi pmi_std pmi_dpct pmi_std_delta trade_Q1_Q2*

save "${root}\temp\reg_panel_trade_pmi_MONTHS.dta", replace

********************************************************************************
********************************************************************************
*Prepare Konsumentenstimmung
********************************************************************************
********************************************************************************

import delimited "${root}\data\PMI_KS_Data\cons_dta_raw.csv", clear 

foreach var of varlist alsurv0002- cnsurv0052 cosurv1000- zasurv0018 {
	local var2 = substr("`var'", 1,2)
	rename `var' country_`var2'
}

foreach var of varlist cons2_cy_tot_cof_bs_m-cons2_ro_tot_cof_bs_m {
	local var2 = substr("`var'", 7,2)
	rename `var' country_`var2'
}

*Date variable 
split time, parse("-")
destring time1, gen(year)
destring time2, gen(month)
destring time3, gen(day)

drop time*

gen date=mdy(month, day, year)
format date %tddd.nn.YY
sort date

*MAIN KST Variable 
foreach var of varlist country* {
	sum `var'
	gen `var'_std=(`var'-r(mean))/r(sd)
}


keep if year>2018
order date year month day
rename country_*_std country_std_*

reshape long country_ country_std_, i(date) j(country) string

rename country_ kst
rename country_std_ kst_std
replace country=strupper(country)
egen land_id=group(country)
rename country land_iso2

order land_iso2 date year month day kst kst_std
sort land_iso2 date year month day kst kst_std

*Main Time ID
gen time_monat=month
replace time_monat=month+12 if year==2020

tsset land_id time_monat

foreach var of varlist kst {
	gen `var'_dpct=(`var'-l.`var')/(l.`var')*100
}

foreach var of varlist kst_std {
	gen `var'_delta=(`var'-l.`var')
}

keep if year==2020
keep if month<=6
order land_iso2 year kst kst_std kst_dpct kst_std_delta
drop land_id

merge 1:1 land_iso2 time_monat using "${root}\temp\reg_panel_trade_pmi_MONTHS.dta"
drop if _merge==1
drop _merge

drop year month day date

order jahr monat time_monat land_str land_iso2 									///
export_chf_mia_dpct import_chf_mia_dpct trade_chf_mia_dpct 						///
pmi pmi_std pmi_dpct pmi_std_delta kst kst_std kst_dpct kst_std_delta trade_Q1_Q2*

save "${root}\temp\reg_panel_trade_pmi_kst_MONTHS.dta", replace


********************************************************************************
********************************************************************************
*Add COVID-19 CASES 
*******************************************************************************
********************************************************************************

import delimited ${root}\data\COVID_DATA\covid-daily-vs-total-cases-per-million_v20200828.csv, ///
delimiter(comma) bindquote(nobind) stripquote(yes) clear 

rename entity land_str
rename code land_iso3
rename dailyconfirmedcasespermillion year
rename totalconfirmedcasespermillioncas  daily_per_mio
rename v6 total_per_mio

*ADAPT MAIN VARIABLES 
replace daily_per_mio = 0 if daily_per_mio == .
replace total_per_mio = 0 if total_per_mio == .
gen total_per_1000=total_per_mio/1000

*Date Variable
split date, parse(" ")
rename date1 month
rename date2 day

drop if month=="Dec"

replace month="1" if month=="Jan"
replace month="2" if month=="Feb"
replace month="3" if month=="Mar"
replace month="4" if month=="Apr"
replace month="5" if month=="May"
replace month="6" if month=="Jun"
replace month="7" if month=="Jul"
replace month="8" if month=="Aug"

destring year, replace
destring month, replace
destring day, replace
drop date

gen date=mdy(month, day, year)
format date %tddd.nn.YY
sort date

gen monday = date - cond(dow(date) == 0, 6, dow(date) - 1)
format monday %tddd.nn.YY

*MERGE COUNTRY IDENTIFIES
merge m:1 land_iso3 using "${root}\temp\UNCTAD_iso2_iso3.dta"
keep if _merge==3
drop _merge 

*!!!SELECT DATE
sort land_str year month day

bysort land_iso2 month: egen max_day=max(day)
keep if day==max_day
keep if month<=6

keep land_str land_iso2 month total_per_1000 total_per_mio
rename month monat
order land_str land_iso2 monat total_per_mio total_per_1000

*MERGE MAIN DATA SET
merge 1:1 land_iso2 monat using "${root}\temp\reg_panel_trade_pmi_kst_MONTHS.dta"
drop if _merge==1
drop _merge 
order land_str land_iso2 jahr monat time_monat										///
export_chf_mia_dpct import_chf_mia_dpct trade_chf_mia_dpct						///
total_per_mio total_per_1000 													///
pmi pmi_std pmi_dpct pmi_std_delta kst kst_std kst_dpct kst_std_delta trade_Q1_Q2*

save "${root}\temp\reg_panel_trade_pmi_kst_covid19_MONTHS.dta", replace

********************************************************************************
********************************************************************************
*Add Government Response
*******************************************************************************
********************************************************************************

import delimited "${root}\data\COVID_DATA\OxCGRT_v20200828.txt", 	///
delimiter(comma) bindquote(nobind) stripquote(yes) clear 

rename countryname land_str
rename countrycode land_iso3
rename stringencyindex stringency_index
rename governmentresponseindex gov_index

keep land_str land_iso3 stringency_index gov_index date

*Date Variable
tostring date, replace
gen year=substr(date, 1,4)
gen month=substr(date, 5,2)
gen day=substr(date, 7,2)

destring year, replace
destring month, replace
destring day, replace
drop date

gen date=mdy(month, day, year)
format date %tddd.nn.YY
sort date

gen date_mo = date - cond(dow(date) == 0, 6, dow(date) - 1)
format date_mo %tddd.nn.YY

sort land_str date_mo
rename month monat

*!!!SELECT DATE
keep if date<=td(30jun2020)
collapse (mean) stringency_index gov_index, by(land_str land_iso3 monat)

*MERGE COUNTRY IDENTIFIES
merge m:1 land_iso3 using "${root}\temp\UNCTAD_iso2_iso3.dta"
keep if _merge==3
drop _merge 

keep land_iso2 monat stringency_index gov_index

*MERGE MAIN DATA SET
merge 1:1 land_iso2 monat using "${root}\temp\reg_panel_trade_pmi_kst_covid19_MONTHS.dta"
drop _merge 

order land_str land_iso2 jahr monat time_monat									///
export_chf_mia_dpct import_chf_mia_dpct trade_chf_mia_dpct						///
total_per_mio total_per_1000 stringency_index gov_index							///
pmi pmi_std pmi_dpct pmi_std_delta kst kst_std kst_dpct kst_std_delta trade_Q1_Q2*

save "${root}\temp\reg_panel_trade_pmi_kst_covid19_MONTHS.dta", replace

********************************************************************************
********************************************************************************
*ADD Population Data
********************************************************************************
********************************************************************************

import excel "${root}\data\Country_Data_Other\Population_Worldbank.xls",		///
 sheet("Data") cellrange(A5:BL268) clear

rename A land_str
rename B land_iso3
rename BL population_2019

keep land* population

gen population_2019_mio=population_2019/(1000*1000)
drop population_2019

merge 1:1 land_iso3 using "${root}\temp\UNCTAD_iso2_iso3.dta", keepusing(land_iso2)
keep if _merge==3
drop _merge 

merge 1:m land_iso2 using "${root}\temp\reg_panel_trade_pmi_kst_covid19_MONTHS.dta"
drop if _merge==1
drop _merge

order land_str land_iso2 land_iso3 	jahr monat time_monat						///
export_chf_mia_dpct import_chf_mia_dpct trade_chf_mia_dpct 						///
total_per_mio total_per_1000 stringency_index gov_index							///
pmi pmi_std pmi_dpct pmi_std_delta kst kst_std kst_dpct kst_std_delta			///
population_2019_mio trade_Q1_Q2*

save "${root}\temp\reg_panel_trade_pmi_kst_covid19_pop_MONTHS.dta", replace


********************************************************************************
********************************************************************************
*Prepare Exchange Rates
********************************************************************************
********************************************************************************

import excel "${root}\data\Country_Data_Other\currencies_all.xlsx", sheet("m") clear
drop in 1

rename B date


foreach var of varlist C-BE {
     rename `var' `=strtoname(`var'[1])'1
}

foreach var of varlist AE1-US1 {
     rename `var' fx`=substr(`var',1,2)'
}

drop if date==""
drop if date=="Region"

reshape long fx, i(date) j(land_iso2) string

destring fx, replace
split date, parse("/")
rename date1 monat
rename date2 tag
rename date3 jahr
drop tag date

destring jahr, replace
destring monat, replace

order land_iso2 jahr monat fx
sort land_iso2 jahr monat fx

gen time=.
replace time=monat if jahr==2018
replace time=monat+12 if jahr==2019
replace time=monat+24 if jahr==2020

egen id=group(land_iso2)

compress

tsset id time

gen fx_dpct = (fx-l12.fx)/l12.fx*100
drop if jahr<2020
drop if monat>6

merge 1:1 land_iso2 jahr monat using "${root}\temp\reg_panel_trade_pmi_kst_covid19_pop_MONTHS.dta"
drop if _merge==1
drop _merge

order land_str land_iso2 land_iso3 	jahr monat time_monat						///
export_chf_mia_dpct import_chf_mia_dpct trade_chf_mia_dpct 						///
total_per_mio total_per_1000 stringency_index gov_index							///
pmi pmi_std pmi_dpct pmi_std_delta kst kst_std kst_dpct kst_std_delta 			///
fx fx_dpct population_2019_mio trade_Q1_Q2*

save "${root}\temp\reg_panel_trade_pmi_kst_covid19_pop_fx_MONTHS.dta", replace

********************************************************************************
********************************************************************************
*MONTHLY CHANGE IN TOTAL CASES
********************************************************************************
********************************************************************************

use "${root}\temp\reg_panel_trade_pmi_kst_covid19_pop_fx_MONTHS.dta", clear

sort land_iso2  monat

egen land_id=group(land_iso2)

tsset land_id monat

foreach var of varlist total_per_mio total_per_1000 {
    gen l`var'=l.`var'
	replace `var'=`var'-l`var' if l`var'!=.
	drop l`var'
 
}

save "${root}\temp\reg_panel_trade_pmi_kst_covid19_pop_fx_MONTHS.dta", replace

********************************************************************************
********************************************************************************
*REGRESSIONS
********************************************************************************
********************************************************************************

use "${root}\temp\reg_panel_trade_pmi_kst_covid19_pop_fx_MONTHS.dta", clear

*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
*SELECT CUTOFF TRADE
global EST_SAMPLE "trade_chf_mia_2019>0.5 & trade_Q1_Q2_25mio==6"
*!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

gen top10=1 if inlist(land_iso2, "DE", "IT", "FR", "AT","GB", "NL", "ES") |		///
inlist(land_iso2, "US","JP", "CN")


*Table 2: EXPORTE

*Column 2
reg export_chf_mia_dpct total_per_1000 i.monat if $EST_SAMPLE, cluster(land_id) 
codebook land_iso2 if e(sample)==1
capture gen esample_covid=1 if e(sample)==1

*Column 3
reg export_chf_mia_dpct stringency_index i.monat if esample_covid==1, cluster(land_id)
codebook land_iso2 if e(sample)==1

*Column 4
reg export_chf_mia_dpct total_per_1000 stringency_index  i.monat if esample_covid==1 , cluster(land_id)
codebook land_iso2 if e(sample)==1

*Column 5
reg export_chf_mia_dpct kst_std total_per_1000 stringency_index i.monat if esample_covid==1, cluster(land_id) 
codebook land_iso2 if e(sample)==1
capture  gen esample_kst=1 if e(sample)==1

*Column 1
reg export_chf_mia_dpct kst_std i.monat if esample_kst==1, cluster(land_id)
codebook land_iso2 if e(sample)==1


*Table 3: IMPORTE
*Column 2
reg import_chf_mia_dpct total_per_1000  i.monat if esample_covid==1, cluster(land_id)
codebook land_iso2 if e(sample)==1

*Column 3
reg import_chf_mia_dpct stringency_index i.monat if esample_covid==1, cluster(land_id)
codebook land_iso2 if e(sample)==1

*Column 4
reg import_chf_mia_dpct stringency_index total_per_1000 i.monat if esample_covid==1, cluster(land_id)
codebook land_iso2 if e(sample)==1

*Column 5
reg import_chf_mia_dpct pmi_std stringency_index total_per_1000 i.monat if esample_covid==1, cluster(land_id)
capture  gen esample_pmi=1 if e(sample)==1
codebook land_iso2 if e(sample)==1

*Column 1
reg import_chf_mia_dpct pmi_std i.monat if esample_pmi==1, cluster(land_id)
codebook land_iso2 if e(sample)==1



*descriptives
sum export_chf_mia_dpct if esample_covid==1, d
sum import_chf_mia_dpct if esample_covid==1, d
sum total_per_1000 if esample_covid==1
sum stringency_index  if esample_covid==1
sum kst_std if esample_covid==1
sum pmi_std if esample_covid==1

